#!/user/bin/env python3
# -*- coding: utf-8 -*-

import pymysql
from sshtunnel import SSHTunnelForwarder
from Common.getpath import get_root_path
from Common.yaml_util import *


class getmysql:

    def __init__(self, databse=get_root_path() + "\\Configs\\HostConfig.yaml"):
        databse_config = YamlUtil(databse).read_yaml()[0]["database_config"]
        self.host = databse_config["host"]
        self.user = databse_config['user']
        self.password = databse_config["password"]
        self.port = databse_config["port"]
        self.database = databse_config['database']
        self.ssh = databse_config["ssh"]

    # 通过ssh链接mysql的方法
    def ssh_to_mysql(self):
        ssh_host = self.ssh["ssh_host"]
        ssh_user = self.ssh["ssh_user"]
        ssh_password = self.ssh["ssh_password"]
        # ssh服务器配置
        server = SSHTunnelForwarder(
            ssh_address_or_host=(ssh_host, 22),  # 数据库对应服务器的地址与账号密码
            ssh_username=ssh_user,
            ssh_password=ssh_password,
            remote_bind_address=(self.host, 3306),
            local_bind_address=('127.0.0.1', 13306))  # 端口号不能带引号
        server.start()  # 启用SSH
        # 数据库账户信息设置
        db = pymysql.connect(
            user=self.user,
            password=self.password,
            host='127.0.0.1',  # 必须为127.0.0.1
            port=13306,
            database=self.database,
            charset='utf8')
        return db

    # 通过账号密码直连mysql
    def to_mysql(self):
        db = pymysql.connect(host=self.host,
                             user=self.user,
                             password=self.password,
                             port=self.port,
                             database=self.database
                             )
        return db

    def run_sql(self, sql):
        if self.ssh is None:
            db = self.to_mysql()
            cursor = db.cursor()
            cursor.execute(sql.encode('utf8'))  # 执行SQL
            data = cursor.fetchall()  # 获取查询结果
            # 关闭数据库连接
            cursor.close()
            return data
        else:
            db = self.ssh_to_mysql()
            cursor = db.cursor()
            cursor.execute(sql.encode('utf8'))  # 执行SQL
            data = cursor.fetchall()  # 获取查询结果
            # 关闭数据库连接
            cursor.close()
            return data
